﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using GSP = gudusoft.gsqlparser;
using Dimok.Web.UI;

namespace Data.DataSources {
	public class FilterBuilderDataSource : ObjectDataSourceBase {
		public static DataView GetColumns(string sql) {
			SqlDataAdapter da = new SqlDataAdapter(sql, Dimok.User.UserObject.dbRaw);
			DataSet ds = new DataSet();
			da.FillSchema(ds, SchemaType.Source);
			DataTable dtOut = new DataTable();
			dtOut.Columns.Add("Name");
			dtOut.Columns.Add("Value");
			dtOut.Columns.Add("Type");
			dtOut.Columns.Add("TypeRaw");
			dtOut.Rows.Add("", "");
			foreach (DataColumn col in ds.Tables[0].Columns)
				dtOut.Rows.Add(
					col.ColumnName,
					col.ColumnName.ToUpper(),
					col.DataType != typeof(string) && col.DataType != typeof(DateTime)? "Number":col.DataType.Name,
					col.DataType.Name
					);
			dtOut.DefaultView.Sort = "Name";
			return dtOut.DefaultView;
		}
		public GridFilterDS.FilterBuilderDataTable GetFilter(string SQL,string FilterString, GridFilterDS.FilterBuilderDataTable FilterTable) {
			if (FilterTable != null) return FilterTable;
			GridFilterDS.FilterBuilderDataTable dtOut =
				FilterString != "" ? ParseFilterString(SQL,FilterString) : new GridFilterDS.FilterBuilderDataTable();
			//dtOut.Columns.Add("OperandType",typeof(string),"IIF(Operand IN('>','<','>=','<=','<>','=','BETWEEN','LIKE'),'U','X')");
			dtOut.AddFilterBuilderRow("", "LIKE", "", "","");
			return dtOut;
		}

		#region Parse Filter
		private static string GetColumnType(DataTable dtColumns, string FieldName) {
			return dtColumns.Select("Name='" + FieldName + "'")[0]["Type"] + "";
		}
		private static string ParseIn(string inString,string quote) {
			return quote + string.Join(quote + "," + quote, inString.Split('\n')) + quote;
		}
		public static string ParseFilterTable(string SQL, GridFilterDS.FilterBuilderDataTable FilterTable) {
			if (FilterTable == null) return "";
			DataTable dtColumns = GetColumns(SQL).Table;
			List<string> lst = new List<string>();
			foreach (GridFilterDS.FilterBuilderRow rowFB in FilterTable)
				if (rowFB.FieldName == "" || rowFB.Value1 == "") continue;
				else if (rowFB.Operand.Contains("BETWEEN") && rowFB.Value2 == "") continue;
				else {
					string fieldType = GetColumnType(dtColumns, rowFB.FieldName);
					string q = fieldType == "String" || fieldType == "DateTime" || rowFB.Operand.Contains("LIKE") ? "'" : "";
					string p = rowFB.Operand.Contains("LIKE") ? "%" : "";
					string expr = Data.SQL.DBObjectNameSafe(rowFB.FieldName) + " " + rowFB.Operand + " " + (rowFB.Value1.Contains("\n") ? "(" + ParseIn(rowFB.Value1, q) + ")" : q + p + rowFB.Value1 + p + q);
					if (rowFB.Value2 != "") expr += " AND " + q + rowFB.Value2 + q;
					lst.Add(expr);
				}
			return string.Join(" AND ", lst.ToArray());
		}
		private static string ParseValue(string value) {
			return Regex.Match(value, @"^['%]*(?<expr>.+?)['%]*$").Groups["expr"].Value;
		}
		private static string ParseFieldName(string name) {
			return Regex.Match(name, @"^[[]*(?<expr>.+?)[\]]*$").Groups["expr"].Value;
		}
		private static string ParseParenthesis(GSP.TLzCustomExpression expr) {
			List<string> lst = new List<string>();
			for (expr = (GSP.TLzCustomExpression)expr.lexpr; expr != null; expr = (GSP.TLzCustomExpression)expr.rexpr)
				lst.Add(ParseValue(expr.lexpr.AsText));
			return string.Join("\n", lst.ToArray());
			//gudusoft.gsqlparser.TLzOpType.Expr_Parenthesis
		}
		private static GridFilterDS.FilterBuilderRow ParseTLzFilter(GSP.TLzCustomExpression filter) {
			if (filter.oper == gudusoft.gsqlparser.TLzOpType.Expr_NOT) {
				GridFilterDS.FilterBuilderRow expParts = ParseTLzFilter((GSP.TLzCustomExpression)filter.rexpr);
				return expParts;
			}
			string field = ParseFieldName(filter.lexpr.AsText);
			string oper = filter.opname.AsText.ToUpper();
			string value1 = ParseValue(filter.rexpr.AsText);
			string value2 = "";
			switch (filter.oper) {
				case gudusoft.gsqlparser.TLzOpType.Expr_NotLike:
					oper = "NOT LIKE"; break;
				case gudusoft.gsqlparser.TLzOpType.Expr_NotBetween:
					oper = "NOT BETWEEN"; break;
				case gudusoft.gsqlparser.TLzOpType.Expr_NotIn:
					oper = "NOT IN"; break;
			}
			switch(((GSP.TLzCustomExpression)filter.rexpr).oper){
				case GSP.TLzOpType.Expr_BetweenTo:
					value1 = ParseValue(((GSP.TLzCustomExpression)(filter.rexpr)).lexpr.AsText);
					value2 = ParseValue(((GSP.TLzCustomExpression)(filter.rexpr)).rexpr.AsText);
					break;
				case gudusoft.gsqlparser.TLzOpType.Expr_Parenthesis:
					value1 = ParseParenthesis((GSP.TLzCustomExpression)filter.rexpr);
					break;
			}
			return new GridFilterDS.FilterBuilderDataTable().AddFilterBuilderRow(field, oper, value1, value2,"");
		}
		public static GridFilterDS.FilterBuilderDataTable ParseFilterString(string SQL, string FilterString) {
			gudusoft.gsqlparser.TGSqlParser sp = new gudusoft.gsqlparser.TGSqlParser(gudusoft.gsqlparser.TDbVendor.DbVMssql);
			sp.SqlText.Text = "SELECT * FROM T WHERE 1=1 AND " + FilterString;
			sp.Parse();
			List<GSP.TLzCustomExpression> lstWheres = new List<GSP.TLzCustomExpression>();
			GSP.TLzCustomExpression rExp = sp.SqlStatements[0].WhereClause as GSP.TLzCustomExpression;
			for (; rExp != null && ((GSP.TLzCustomExpression)rExp.rexpr).rexpr != null; rExp = rExp.lexpr as GSP.TLzCustomExpression)
				lstWheres.Add((GSP.TLzCustomExpression)rExp.rexpr);

			GridFilterDS.FilterBuilderDataTable dtFB = new GridFilterDS().FilterBuilder;
			lstWheres.Reverse();
			DataTable dtColumns = GetColumns(SQL).Table;
			foreach (GSP.TLzCustomExpression filter in lstWheres) {// Regex.Split(filters, " AND "))
				GridFilterDS.FilterBuilderRow rowFB = ParseTLzFilter(filter);
				string type = dtColumns.Select("Name='" + rowFB.FieldName + "'")[0]["Type"] + "";
				dtFB.AddFilterBuilderRow(rowFB.FieldName.ToUpper(), rowFB.Operand, rowFB.Value1, rowFB.Value2,type);
			}
			return dtFB;
		}
		#endregion

		public static void UpdateEx(string SQL,int ID, string UpdateName, string UpdateValue, GridFilterDS.FilterBuilderDataTable FilterTable) {
			GridFilterDS.FilterBuilderRow row = FilterTable.FindByID(ID);
			row[UpdateName] = UpdateValue;
			if (UpdateName.ToLower() == FilterTable.FieldNameColumn.ColumnName.ToLower()) {
				DataTable dtColumns = GetColumns(SQL).Table;
				row[FilterTable.FieldTypeColumn] = dtColumns.Select("Name='" + UpdateValue + "'")[0]["Type"] + "";
			}
			if (row.FieldName != "" && GetRowIndex(ID,FilterTable) == FilterTable.Count)
				FilterTable.AddFilterBuilderRow("", "LIKE", "", "", "" );
		}
		public void DeleteEx(int ID, GridFilterDS.FilterBuilderDataTable FilterTable) {
			if (GetRowIndex(ID,FilterTable) == FilterTable.Count) return;
			FilterTable.FindByID(ID).Delete();
			FilterTable.AcceptChanges();
		}
		public static void Clear(GridFilterDS.FilterBuilderDataTable FilterTable) {
			while (FilterTable.Count > 1) FilterTable.Rows.RemoveAt(0);
		}
		static int GetRowIndex(int ID, GridFilterDS.FilterBuilderDataTable FilterTable) {
			for (int r = 0; r < FilterTable.Count; r++)
				if (FilterTable[r].ID == ID) return ++r;
			throw new Exception("ID:" + ID + " not found in Filter Table.");
		}
		public static DataTable GetOperads() {
			DataTable dt = new DataTable();
			dt.Columns.Add("Value");
			dt.Columns.Add("Name");
			dt.Columns.Add("Desc1");
			dt.Columns.Add("Desc2");
			dt.Rows.Add("LIKE", "CONTAINS", "");
			dt.Rows.Add("NOT LIKE", "DOES NOT CONTAIN", "");
			dt.Rows.Add("<", "IS LESS", "");
			dt.Rows.Add("<=", "IS LESS OR EQUAL", "");
			dt.Rows.Add("<>", "IS NOT EQUAL", "");
			dt.Rows.Add("=", "EQUALS", "");
			dt.Rows.Add("IN", "IN", "IsValue 1\nOrValue 2\nOrValue N");
			dt.Rows.Add("NOT IN", "NOT IN", "IsNotValue 1\nAndNotValue 2\nAndNotValue N");
			dt.Rows.Add(">", "IS GREATER", "");
			dt.Rows.Add(">=", "IS GREATER OR EQUIAL", "");
			dt.Rows.Add("BETWEEN", "BETWEEN", "FLOOR", "CEILING");
			dt.Rows.Add("NOT BETWEEN", "NOT BETWEEN", "FLOOR", "CEILING");
			return dt;
		}
	}
}